﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;


/// <summary>
///封装连接数据库，执行SQL语句的类
/// </summary>
public class SqlExecutor
{
    string _connectionString;//用于保存数据库的链接字符串
    public SqlExecutor()
    {
        //
        //构造函数的功能是从web.config文件中读取数据库连接字符串
        //
        _connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
    }

    /// <summary>
    /// 用于执行insert、update、delete等修改类sql语句
    /// </summary>
    /// <param name="cmd"></param>
    /// <returns></returns>
    public bool UpdateCmd(string cmd)
    {
        //创建sql连接
        SqlConnection myConnection = new SqlConnection(_connectionString);
        //创建sql命令
        SqlCommand myCommand = new SqlCommand(cmd, myConnection);
        try
        {
            //打开连接
            myConnection.Open();
            //执行sql语句，并用result保存影响行数
            int result = myCommand.ExecuteNonQuery();
            if (result > 0) return true;//如果影响行数大于0，则表示执行成功，返回true
            return false;//影响行数为0，执行失败，返回false
        }
        catch (Exception e)
        {
            //异常情况，返回false
            return false;
        }
        finally
        {
            //关闭连接
            if (myConnection.State == ConnectionState.Open)myConnection.Close();
        }
    }

    /// <summary>
    /// 执行select等查询类的sql语句
    /// </summary>
    /// <param name="cmd"></param>
    /// <returns></returns>
    public DataTable SelectCmd(string cmd)
    {
        //创建sql连接
        SqlConnection myConnection = new SqlConnection(_connectionString);
        //创建sql命令
        SqlCommand myCommand = new SqlCommand(cmd, myConnection);
        try
        {
            
            //用一个DataTable保存结果
            DataTable result = new DataTable();
            //用一个SqlDataAdapter来执行命令
            SqlDataAdapter myAdapter = new SqlDataAdapter();
            myAdapter.SelectCommand = myCommand;
            //打开连接
            myConnection.Open();
            myAdapter.Fill(result);//执行命令
            return result;
        }
        catch (Exception e)
        {
            //异常情况，返回空的DataTable
            DataTable error = new DataTable();
            return error;
        }
        finally
        {
            //关闭连接
            if (myConnection.State == ConnectionState.Open) myConnection.Close();
        }
    }
}
